global root_dir = "`1'"

include "$root_dir/code/config/config.do"


cap noi log using ${log_dir}/import_swiss_data.log, replace name(dat)

*Handle empty arguments
global arg1 = cond("`2'" == "___EMPTY___", "", "`2'")
global arg2 = cond("`3'" == "___EMPTY___", "", "`3'")
global arg3 = cond("`4'" == "___EMPTY___", "", "`4'")
global arg4 = cond("`5'" == "___EMPTY___", "", "`5'")

if "$arg1" != "" {
    global weight_category "$arg1"
    di "Weight category: ${weight_category}"
}

if "$arg2" != "" {
    global weight_versions "$arg2"
    di "Weight versions: ${weight_versions}"
}

if "$arg3" != "" {
    global weight_window "$arg3"
    di "Weight window: ${weight_window}"
}

if "$arg4" != "" {
	global wtype "$arg4"
}
di "${wtype}"
capture noi {
set more off

*Imports Swiss data on wages, working hours, producer prices, and exchange rates.
*Also imports the previously built value added data for Switzerland.
*merges all of this together into a single dataset




********************************************************************************
*1 Import swiss monthly wage data by education group
*******************************************************************************
*Import monthly wages by education group for the years 2014 and 2016.
*needs to be done separately because the data is in a different format than the other years.
*data on education and earnings | Proprietary through BFS/OFS | reformatted by hand for easier import | Received February 4th 2019
import excel ${mow_data_raw}/swiss_data/UNI_ZH_version_vs_MGO.xls, sheet("1994-2016") cellrange(A3:M13) firstrow clear

replace Year=Year[_n-1] if Year==.
replace Sector=Sector[_n-1] if Sector==""

drop if Type==""

save ${mow_data_proc}/swiss.dta, replace

* Import monthly wages by education group for the other years.
* data on education and earnings | Proprietary through BFS/OFS | reformatted by hand for easier import | Received February 4th 2019
import excel ${mow_data_raw}/swiss_data/UNI_ZH_version_vs_MGO.xls, sheet("1994-2016") cellrange(A14:N70) firstrow clear

rename C Type
rename A Year
rename B Sector

* A few numbers are in brackets to indicate that the uncertainty is more than 5 per cent.
* We are not worried about that here. Thus remove the brackets.

replace Formationmissing="9254.66" if _n==4
replace Brevetdenseignement="7742.84" if _n==4
replace Autresformationscomplètes="5447.26" if _n==1
replace Brevetdenseignement="6109.31" if _n==34

destring Brevetdenseignement, replace
destring Formationmissing, replace
destring Autresformationscomplètes, replace

replace Year=Year[_n-1] if Year==.
replace Sector=Sector[_n-1] if Sector==""

drop if Type==""

* Append the data by the year 2014 and 2016.
append using ${mow_data_proc}/swiss.dta

***********************
* Translate from French. 
***********************

* Manufacturing is defined differently for each year. Let's not worry about that.

gen code=""
replace code="TOT" if Sector=="Total économie"
* For the years 2008-XXXX
replace code="D" if Sector=="Industrie manufacturière (10-33 Noga 2008)"		
replace code="D" if Sector=="Industrie manufacturière (15-37 Noga 2002)"
replace code="D" if code=="" & Year==1994

* Educational attaintment

* There was a change in definition (https:*ec.europa.eu/eurostat/statistics-explained/index.php/International_Standard_Classification_of_Education_(ISCED)#Correspondence_between_ISCED_2011_and_ISCED_1997)
* such that

* ISCED 1997 	ISCED 2011 (used from 2014 onwards)

* ISCED 0 		ISCED 01 
* ISCED 0 		ISCED 02
* ISCED 1		ISCED 1
* ISCED 2 		ISCED 2
* ISCED 3		ISCED 3
* ISCED 4		ISCED 4
* ISCED 5		ISCED 5
* ISCED 5		ISCED 6
* ISCED 5 		ISCED 7
* ISCED 6		ISCED 8. 

* Since we are only interested in the period up until 2008, this is pretty straightforward. Swiss ISCED 5-8 
* is high-skil, 3-4 is middle-skill, and 1-2 is low-skill.
* Hence, for our purposes, what matters is the groupings across these three. 

rename HauteécoleuniversitaireUNI Edu_HauteécoleuniversitaireUNI
rename HauteécolespécialiséeHESH Edu_HauteécolespécialiséeHESH
rename Formationprofsupérieureécol Edu_Formationprofsupérieureécol
rename Brevetdenseignement Edu_Brevetdenseignement
rename Maturité Edu_Maturité
rename Formationacquiseenentreprise  Edu_Formationacquiseenentreprise
rename ApprentissagecompletCFC Edu_ApprentissagecompletCFC
rename Sansformationprofcomplète Edu_Sansformationprofcomplète

* Drop the education groups we do not need. 
drop Sector  Formationmissing Autresformationscomplètes Total

* Transform data from matrix to five column table. 
reshape long Edu_, i(Year Type code) j(Education) string
rename Edu_ variable

replace Type="Salaire" if Type=="Salaire " 

reshape wide variable, i(Year code Education) j(Type) string

* Aggregate the education groups into the 3 skill levels. 
replace Education="HS" if Education=="HauteécoleuniversitaireUNI"
replace Education="HS" if Education=="HauteécolespécialiséeHESH"
replace Education="HS" if Education=="Formationprofsupérieureécol"
replace Education="MS" if Education=="Brevetdenseignement"
replace Education="MS" if Education=="Maturité"
replace Education="MS" if Education=="Formationacquiseenentreprise"
replace Education="MS" if Education=="ApprentissagecompletCFC"
replace Education="LS" if Education=="Sansformationprofcomplète"


collapse (mean)  variableSalaire [aw=variableRépartition], by(Year code Education) 
save ${mow_data_proc}/swiss.dta, replace


******************************************************************************************************
* Calculate the average wage across the three diffrent skill levels, weighted by the share of employees.
******************************************************************************************************



* Interpolate the wage data for the following years. 
clear 
input Year 
1995
1997 
1999
2001
2003
2005
2007
2009
2011
2013
2015
end

append using ${mow_data_proc}/swiss.dta
replace code="D" if code==""
replace Education="HS" if Education==""
fillin Year code Education

ipolate variableSalaire Year, by(Education code) generate(salary_monthly)
sleep 1000
save ${mow_data_proc}/swiss.dta, replace

********************************************************************
* Import total working hours per worker (90-100%) in Switzerland. 
********************************************************************
clear 
set obs 1
gen Year=1
save ${mow_data_proc}/swiss_hours.dta, replace

foreach var in 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 {
    * Swiss statistics on average working hours | From BFS/OFS SVOLTA | https://www.bfs.admin.ch/bfs/fr/home/statistiques/travail-remuneration/activite-professionnelle-temps-travail/heures-travail/heures-effectives-travail.assetdetail.31025792.html | Downloaded April 16th 2019
    import excel ${mow_data_raw}/swiss_data/je-f-03.02.03.01.02.02.xlsx, sheet("`var'") cellrange(L10:L10) clear

    gen Year=`var' 
    append using ${mow_data_proc}/swiss_hours.dta
    drop if Year==1
    sleep 2000
    save ${mow_data_proc}/swiss_hours.dta, replace
}

rename L yearly_hours

* Merge the data with the monthly wages.
merge 1:m Year using ${mow_data_proc}/swiss.dta, nogen

* Hourly_wage is the monthly salary multiplied by 12 divided by how many hours the swiss work on average in one year times 1.2, which is the 
* relative difference between salaries and labor costs in Switzerland. 
* In this project, we continuously call labor costs wages. 
gen hourly_wage=salary_monthly*12/yearly_hours*1.2 

save ${mow_data_proc}/swiss.dta, replace

********************************************************************
* Import the producer price data. 
********************************************************************

* Do this for both producer prices in manufacturing and in the whole economy. 
* Manufacturing
clear 
set obs 1
gen Year=1
save ${mow_data_proc}/swiss_producer_price.dta, replace

foreach year in 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 {
    *Swiss data on prices of production | From BFS/ONF | https://www.bfs.admin.ch/bfs/en/home.assetdetail.1840017.html | Downloaded February 20th 2019
    import excel ${mow_data_raw}/swiss_data/je-f-05.04.01.01.xls, sheet("`year'") cellrange(O22:O22) clear
    rename O producer_price 
    gen Year=`year'
    append using ${mow_data_proc}/swiss_producer_price.dta
    drop if Year==1
    sleep 2000
    save ${mow_data_proc}/swiss_producer_price.dta, replace

}

* Take the producer price in 2005 as baseline. Express other years producer price as percentage of the 2005.
gen price_2005=producer_price if Year==2005
egen price_2005_2=total(price_2005)
replace producer_price=producer_price/price_2005_2*100
gen code="D" 

save ${tmp_dir}/temp_base_swiss_prodprice.dta, replace

* Whole economy (Total)

clear 
set obs 1
gen Year=1
save ${mow_data_proc}/swiss_producer_price.dta, replace

foreach year in 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 {

    import excel ${mow_data_raw}/swiss_data/je-f-05.04.01.01.xls, sheet("`year'") cellrange(O10:O10) clear
    rename O producer_price 
    gen Year=`year'
    append using ${mow_data_proc}/swiss_producer_price.dta
    drop if Year==1
    sleep 2500
    save ${mow_data_proc}/swiss_producer_price.dta, replace
}

gen price_2005=producer_price if Year==2005
egen price_2005_2=total(price_2005)
replace producer_price=producer_price/price_2005_2*100
gen code="TOT" 

append using ${tmp_dir}/temp_base_swiss_prodprice.dta


* Merge it with the dataset on wages and working hours. 
merge 1:m code Year using ${mow_data_proc}/swiss.dta, nogen

* Drop variables that are no longer needed. 
keep producer_price Year code Education hourly_wage

* Reshape the data again. 
reshape wide hourly_wage producer_price, i(Year code) j(Education) string
drop producer_priceLS producer_priceMS
rename producer_priceHS producer_price 
gen country="che"

rename producer_price VA_P

* Code just pretends that it comes from WIOD for more consistent handling
gen VA_P_WIOD = VA_P 				

rename hourly_wageHS hs_wage
rename hourly_wageLS ls_wage
rename hourly_wageMS ms_wage
rename Year year
gen ls_wage_WIOD=ls_wage
gen ms_wage_WIOD=ms_wage
gen hs_wage_WIOD=hs_wage


save ${mow_data_proc}/swiss.dta, replace

********************************************************************
* Import exchange rates. 
********************************************************************
*Yearly average Swiss exchange rates CHF to USD | from OECD.Stat | Data extracted on 21 Feb 2019 10:13 UTC (GMT)
import excel ${mow_data_raw}/exchange_rates/swiss_exchange_rate.xlsx, sheet("OECD.Stat export") cellrange(AA3:AX5) firstrow clear

drop if _n==1
rename AA y1990
rename AB y1991 
rename AC y1992
rename AD y1993
rename AE y1994
gen country="che"
reshape long y, i(country) j(year)
rename y exchangerate

* Merge exchange rates with other dataset. 
merge 1:m year using ${mow_data_proc}/swiss.dta, nogen

save ${mow_data_proc}/swiss.dta, replace


*Add the value added measurements for Switzerland generated in another do file. 
use ${mow_data_proc}/swiss.dta, clear
merge 1:1 year code using ${mow_data_proc}/swiss_va_emp.dta
drop _merge
gen va_emp_WIOD = va_emp


*Since we added the exchange rate without any other measurements we have to 
*duplicate the observations 1990 - 1993 to have the exchange rate for D and TOT. 
expand 2 if year<1994 & year>1989
sort year
quietly by year:  gen dup = cond(_N==1,0,_n)
replace code = "D" if dup==1 & code==""
replace code = "TOT" if dup==2 & code==""
drop dup

save ${mow_data_proc}/swiss_import.dta, replace


}
if _rc == 0 {
    display "Execution finished successfully."
}
else {
    display "Execution finished with errors."
}

cap log close dat